package com.wcgksoft.controller.insect;


import com.wcgksoft.common.charts.component.PieDataBean;
import com.wcgksoft.common.factory.ChartsFactory;
import com.wcgksoft.entity.system.EnterpriseProject;
import com.wcgksoft.service.insect.InsectHistoryService;
import net.sf.json.JSONObject;
import org.apache.shiro.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.List;


/**
 *
 * 虫情统计分析
 *
 */
@Controller
@RequestMapping("insect/statisticalAnalysis")
public class InsectStatisticsController {

    @Autowired
    private InsectHistoryService insectHistoryService;


    @RequestMapping("count")
    public String count(ModelMap modelMap,
                        HttpServletRequest request,
                @RequestParam(value = "keywords", required = false) String keywords) {
        System.out.println(keywords);
        EnterpriseProject enterpriseProject =  (EnterpriseProject) SecurityUtils.getSubject().getSession().getAttribute("area");
        Integer areaid = enterpriseProject.getId();


        List<Object> twig = insectHistoryService.getHibernateSession().createSQLQuery("select * from t_insectHistory where areaId='" + areaid + "'").list();
        modelMap.put("twigs",twig.size());
        List<Object> type = insectHistoryService.getHibernateSession().createSQLQuery("select * from t_insectHistory where areaId='" + areaid + "' group by medicament having count(medicament) =1").list();
        List<Object> type1 = insectHistoryService.getHibernateSession().createSQLQuery("select * from t_insectHistory where areaId='" + areaid + "' group by medicament having count(medicament) >1").list();
        modelMap.put("typea",type.size()+type1.size());
        List num = insectHistoryService.getHibernateSession().createSQLQuery("SELECT SUM(dosages) FROM t_insectHistory where areaId='" + areaid + "' ;").list();
        modelMap.put("nums",num.get(0));

        List<Object> insectType = insectHistoryService.getHibernateSession().createSQLQuery("select * from t_insectHistory where areaId='" + areaid + "' group by types having count(types) =1").list();
        List<Object> insectType1 = insectHistoryService.getHibernateSession().createSQLQuery("select * from t_insectHistory where areaId='" + areaid + "' group by types having count(types) >1").list();
        modelMap.put("insectType",insectType.size()+insectType1.size());


        List<PieDataBean> objects3 = new ArrayList<>();
        List<Object[]> lists = insectHistoryService.getHibernateSession().createSQLQuery("SELECT COUNT(*), types from t_insecthistory GROUP BY types").list();
        for (Object[] list12 : lists) {
            PieDataBean pieDataBean = new PieDataBean();
            pieDataBean.setName(list12[1].toString());
            pieDataBean.setValue(list12[0].toString());
            objects3.add(pieDataBean);


        }


        // PieDataBean pieDataBean1 = new PieDataBean();
        List<PieDataBean> objects4 = new ArrayList<>();
        List<Object[]> lists2 = insectHistoryService.getHibernateSession().createSQLQuery("SELECT SUM(dosages),medicament from t_insecthistory GROUP BY medicament ").list();
        for (Object[] objects2 : lists2) {
            PieDataBean pieDataBean1 = new PieDataBean();
            pieDataBean1.setName(objects2[1].toString());
            pieDataBean1.setValue(objects2[0].toString());
            objects4.add(pieDataBean1);
        }

        JSONObject json= ChartsFactory.createPie("病/虫害对比",20,"bottom","bottom","50%",objects3);
        JSONObject json1= ChartsFactory.createPie("药剂用量比",20,"bottom","bottom","50%",objects4);
        modelMap.put("debug",json);
        modelMap.put("debug1",json1);

        if (null == keywords || keywords.equals("")) {


            ArrayList<Object> objects = new ArrayList<>();
            ArrayList<Object> objects1= new ArrayList<>();
            List list = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%01')").list();
            objects.add(list.get(0));
            List list1 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%02')").list();
            objects.add(list1.get(0));
            List list2 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%03')").list();
            objects.add(list2.get(0));
            List list3 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%04')").list();
            objects.add(list3.get(0));
            List list4 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%05')").list();
            objects.add(list4.get(0));
            List list5 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%06')").list();
            objects.add(list5.get(0));
            List list6 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%07')").list();
            objects.add(list6.get(0));
            List list7 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%08')").list();
            objects.add(list7.get(0));
            List list8 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "' and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%09')").list();
            objects.add(list8.get(0));
            List list9 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "' and types='病害' and date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%10')").list();
            objects.add(list9.get(0));
            List list10 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%11')").list();
            objects.add(list10.get(0));
            List list11 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%12')").list();
            objects.add(list11.get(0));


            List lis = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%01')").list();
            objects1.add(lis.get(0));
            List lis1 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%02')").list();
            objects1.add(lis1.get(0));
            List lis2 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%03')").list();
            objects1.add(lis2.get(0));
            List lis3 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%04')").list();
            objects1.add(lis3.get(0));
            List lis4 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%05')").list();
            objects1.add(lis4.get(0));
            List lis5 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%06')").list();
            objects1.add(lis5.get(0));
            List lis6 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%07')").list();
            objects1.add(lis6.get(0));
            List lis7 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%08')").list();
            objects1.add(lis7.get(0));
            List lis8 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "' and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%09')").list();
            objects1.add(lis8.get(0));
            List lis9 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "' and types='虫害' and date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%10')").list();
            objects1.add(lis9.get(0));
            List lis10 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%11')").list();
            objects1.add(lis10.get(0));
            List lis11 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%Y%12')").list();
            objects1.add(lis11.get(0));
            modelMap.put("result",objects);
            modelMap.put("result1",objects1);



        }else {

            ArrayList<Object> objects = new ArrayList<>();
            ArrayList<Object> objects1 = new ArrayList<>();
            List list = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%01')").list();
            objects.add(list.get(0));
            List list1 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%02')").list();
            objects.add(list1.get(0));
            List list2 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%03')").list();
            objects.add(list2.get(0));
            List list3 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%04')").list();
            objects.add(list3.get(0));
            List list4 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%05')").list();
            objects.add(list4.get(0));
            List list5 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%06')").list();
            objects.add(list5.get(0));
            List list6 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%07')").list();
            objects.add(list6.get(0));
            List list7 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%08')").list();
            objects.add(list7.get(0));
            List list8 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "' and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%09')").list();
            objects.add(list8.get(0));
            List list9 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "' and types='病害' and date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%10')").list();
            objects.add(list9.get(0));
            List list10 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%11')").list();
            objects.add(list10.get(0));
            List list11 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='病害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%12')").list();
            objects.add(list11.get(0));


            List lis = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%01')").list();
            objects1.add(lis.get(0));
            List lis1 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%02')").list();
            objects1.add(lis1.get(0));
            List lis2 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%03')").list();
            objects1.add(lis2.get(0));
            List lis3 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%04')").list();
            objects1.add(lis3.get(0));
            List lis4 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%05')").list();
            objects1.add(lis4.get(0));
            List lis5 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%06')").list();
            objects1.add(lis5.get(0));
            List lis6 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%07')").list();
            objects1.add(lis6.get(0));
            List lis7 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%08')").list();
            objects1.add(lis7.get(0));
            List lis8 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "' and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%09')").list();
            objects1.add(lis8.get(0));
            List lis9 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "' and types='虫害' and date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%10')").list();
            objects1.add(lis9.get(0));
            List lis10 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%11')").list();
            objects1.add(lis10.get(0));
            List lis11 = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*) from t_insectHistory where areaId='" + areaid + "'and types='虫害' and  date_format(releaseTime,'%Y%m')=DATE_FORMAT(NOW(), '%" +keywords+ "%12')").list();
            objects1.add(lis11.get(0));
            modelMap.put("result",objects);
            modelMap.put("result1",objects1);



            //饼状图时间
//            List<PieDataBean> objects3 = new ArrayList<>();
//            List<Object[]> lists = insectHistoryService.getHibernateSession().createSQLQuery("select COUNT(*), types from t_insecthistory where DATE_FORMAT(releaseTime, '%Y')=DATE_FORMAT(NOW(), '%"+keywords+"')  GROUP BY types").list();
//            for (Object[] list12 : lists) {
//                PieDataBean pieDataBean = new PieDataBean();
//                pieDataBean.setName(list12[1].toString());
//                pieDataBean.setValue(list12[0].toString());
//                objects3.add(pieDataBean);
//
//            }
//
//            List<PieDataBean> objects4 = new ArrayList<>();
//            List<Object[]> lists2 = insectHistoryService.getHibernateSession().createSQLQuery("SELECT SUM(dosages),medicament from t_insecthistory where DATE_FORMAT(releaseTime, '%Y')=DATE_FORMAT(NOW(), '%"+keywords+"')  GROUP BY medicament").list();
//
//            for (Object[] objects2 : lists2) {
//                PieDataBean pieDataBean1 = new PieDataBean();
//                pieDataBean1.setName(objects2[1].toString());
//                pieDataBean1.setValue(objects2[0].toString());
//                objects4.add(pieDataBean1);
//            }
//            JSONObject json= ChartsFactory.createPie("病/虫害对比",20,"bottom","bottom","50%",objects3);
//            JSONObject json1= ChartsFactory.createPie("药剂用量比",20,"bottom","bottom","50%",objects4);
//            modelMap.put("debug",json);
//            modelMap.put("debug1",json1);


        }

        //String[] data1 = new String[list.size()];
        //String[] s ={"1月","2月","3","4","5","6","7","8","9","10","11","12"};
        //List<ChartsBarDataBean> beans = new ArrayList<>();
        //ChartsBarDataBean dataBean = new ChartsBarDataBean();
        //dataBean.setData(data1);
        //dataBean.setName("病/虫害比");
        //beans.add(dataBean);
        //request.getSession().setAttribute("ringJson", json);//*


        return "pestDetection/statisticalAnalysis";
    }




}
